﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataAccessLayer;
using System.Data;
using System.Configuration;
using DTO;

namespace DAL
{
    public class SanPhamDAL
    {
        private DBManager _dbmanager;

        #region Phương thức khởi tạo mặc định
        public SanPhamDAL()
        {
            string _connectionInfo = ConfigurationManager.AppSettings["ConnectionInfo"];
            _dbmanager = new DBManager(DataProvider.SqlServer, _connectionInfo);
        }
        #endregion

        #region Phương thức tạo 1 sản phẩm từ Data Reader
        private SanPhamDTO CreateSanPhamFromReader(IDataReader reader)
        {
            SanPhamDTO _spDTO = new SanPhamDTO();
            _spDTO.MaSanPham = reader["MaSanPham"].ToString();
            _spDTO.MaNhomSanPham = reader["MaNhomSanPham"].ToString();
            _spDTO.MaNhaSanXuat = reader["MaNhaSanXuat"].ToString();
            _spDTO.TenSanPham = reader["TenSanPham"].ToString();
            _spDTO.ThongSoKyThuat = Convert.ToString(reader["ThongSoKyThuat"].ToString());
            _spDTO.GiaNhap = Convert.ToDecimal(reader["GiaNhap"].ToString());
            _spDTO.GiaBan = Convert.ToDecimal(reader["GiaBan"].ToString());
            _spDTO.KhuyenMai = Convert.ToInt32(reader["KhuyenMai"].ToString());
            _spDTO.SoLuongTonKho = Convert.ToInt32(reader["SoLuongTonKho"].ToString());
            _spDTO.TinhTrang = Convert.ToInt32(reader["TinhTrang"].ToString());
            _spDTO.SoLuotMua = Convert.ToInt32(reader["SoLuotMua"].ToString());
            _spDTO.SoLuotXem = Convert.ToInt32(reader["SoLuotXem"].ToString());
            _spDTO.NgayDang = Convert.ToDateTime(reader["NgayDang"].ToString());
            _spDTO.BaoHanh = reader["BaoHanh"].ToString();
            return _spDTO;
        }
        #endregion

        #region Lấy toàn bộ thông tin sản phẩm
        public List<SanPhamDTO> GetListSanPham()
        {
            List<SanPhamDTO> _lst = new List<SanPhamDTO>();
            IDataReader _reader = null;
            try
            {
                _dbmanager.Open();

                _reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getListSanPham");
                while (_reader.Read())
                    _lst.Add(this.CreateSanPhamFromReader(_reader));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (_reader != null)
                    _reader.Close();
                _dbmanager.Close();
            }
            return _lst;
        }
        #endregion

        #region Lay thong tin 1 san pham
        public SanPhamDTO GetSanPham(string masp)
        {
            SanPhamDTO _spDTO = new SanPhamDTO();
            IDataReader _reader = null;
            try
            {
                _dbmanager.Open();

                _dbmanager.CreateParameters(1);
                _dbmanager.AddParameters(0, "@ma_sp", masp);
                _reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getSanPham");
                while (_reader.Read())
                    _spDTO = this.CreateSanPhamFromReader(_reader);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (_reader != null)
                    _reader.Close();
                _dbmanager.Close();
            }
            return _spDTO;
        }
        #endregion

        #region Comment
        //// lay thong tin 1 san pham co hinh anh
        //public List<SanPhamFullDTO> getSanPhamFull(string masp)
        //{
        //    List<SanPhamFullDTO> lstSPF = new List<SanPhamFullDTO>();
        //    IDataReader reader = null;
        //    try
        //    {
        //        _dbmanager.Open();

        //        _dbmanager.CreateParameters(1);
        //        _dbmanager.AddParameters(0, "@masp", masp);
        //        reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "sp_getSanPham");
        //        while (reader.Read())
        //        {
        //            SanPhamDTO sp = new SanPhamDTO();
        //            SanPhamFullDTO spF = new SanPhamFullDTO();
        //            sp = createSanPhamFromReader(reader);

        //            spF.MaSanPham = sp.MaSanPham;
        //            spF.TenSanPham = sp.TenSanPham;
        //            spF.TinhTrang = sp.TinhTrang;
        //            spF.BaoHanh = sp.BaoHanh;
        //            spF.GiaBan = sp.GiaBan;
        //            spF.ThongSoKyThuat = sp.ThongSoKyThuat;
        //            spF.SoLuongTonKho = sp.SoLuongTonKho;

        //            Session["ThongSoKyThuat"] = sp.ThongSoKyThuat;

        //            HinhAnhDAL haDAL = new HinhAnhDAL();
        //            List<HinhAnhDTO> lstHA = new List<HinhAnhDTO>();
        //            lstHA = haDAL.getListHinhAnhMotSanPham(spF.MaSanPham);

        //            spF.LstHinhAnh = lstHA;
        //            lstSPF.Add(spF);
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //            reader.Close();
        //        _dbmanager.Close();
        //    }
        //    return lstSPF;
        //}

        //// lay thong tin san pham cho gio hang
        //public GioHangDTO getSanPhamVaoGioHang(string masp)
        //{
        //    GioHangDTO gh = new GioHangDTO();
        //    IDataReader reader = null;
        //    try
        //    {
        //        _dbmanager.Open();

        //        _dbmanager.CreateParameters(1);
        //        _dbmanager.AddParameters(0, "@masp", masp);
        //        reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "sp_getSanPhamVaoGioHang");
        //        while (reader.Read())
        //        {
        //            gh.MaSanPham = reader["masanpham"].ToString();
        //            gh.TenSanPham = Convert.ToString(reader["TenSanPham"]);
        //            gh.SoLuong = (int)reader["SoLuongTonKho"];
        //            gh.GiaBan = Convert.ToDecimal(reader["GiaBan"]);
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //            reader.Close();
        //    }
        //    return gh;
        //}


        //public SanPhamFullDTO createSanPhamFullFromReader(IDataReader reader)
        //{
        //    SanPhamFullDTO sp = new SanPhamFullDTO();
        //    sp.MaSanPham = Convert.ToString(reader["MaSanPham"]);
        //    sp.TenSanPham = Convert.ToString(reader["TenSanPham"]);
        //    sp.GiaBan = Convert.ToDecimal(reader["GiaBan"]);
        //    sp.BaoHanh = reader["BaoHanh"].ToString();
        //    sp.ThongSoKyThuat = Convert.ToString(reader["ThongSoKyThuat"]);

        //    List<HinhAnhDTO> lstHA = new List<HinhAnhDTO>();
        //    HinhAnhDAL haDAL = new HinhAnhDAL();
        //    lstHA = haDAL.getListHinhAnhMotSanPham(sp.MaSanPham);
        //    sp.TenHinhAnh = lstHA[0].TenHinhAnh;

        //    return sp;
        //}


        //// lấy danh sách sản phẩm mới 
        //public List<SanPhamFullDTO> getListSanPhamMoi()
        //{
        //    List<SanPhamFullDTO> lst = new List<SanPhamFullDTO>();
        //    IDataReader reader = null;

        //    try
        //    {
        //        _dbmanager.Open();
        //        reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "sp_getListSanPhamMoi");

        //        while (reader.Read())
        //        {
        //            lst.Add(createSanPhamFullFromReader(reader));
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //            reader.Close();
        //        _dbmanager.Close();
        //    }
        //    return lst;
        //}

        //// lay danh sach san pham khuyen mai
        //public List<SanPhamFullDTO> getListSanPhamKhuyenMai()
        //{
        //    List<SanPhamFullDTO> lst = new List<SanPhamFullDTO>();
        //    IDataReader reader = null;

        //    try
        //    {
        //        _dbmanager.Open();
        //        reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getListSanPhamKhuyenMai");

        //        while (reader.Read())
        //        {
        //            lst.Add(createSanPhamFullFromReader(reader));
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //            reader.Close();
        //        _dbmanager.Close();
        //    }
        //    return lst;
        //}
        
        //// thu nghiem lay san pham co list hinh anh
        //public List<SanPhamFullDTO> getListSanPhamHot()
        //{
        //    List<SanPhamFullDTO> lst = new List<SanPhamFullDTO>();
        //    IDataReader reader = null;
        //    CommonDAL cmDAL = new CommonDAL();
        //    int soluotmua = cmDAL.laySoLuotMuaGoiLaHot();

        //    try
        //    {
        //        _dbmanager.Open();

        //        _dbmanager.CreateParameters(1);
        //        _dbmanager.AddParameters(0, "@soluotmua", soluotmua);
        //        reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "sp_getListSanPhamHot");
                
        //        while (reader.Read())
        //        {
        //            lst.Add(createSanPhamFullFromReader(reader));
        //        }
                
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //            reader.Close();
        //        _dbmanager.Close();
        //    }
        //    return lst;
        //}


        //// lay danh sách sản phẩm theo mã nhóm
        //public List<SanPhamFullDTO> getListSanPhamTheoNhom(string manhom)
        //{
        //    List<SanPhamFullDTO> lst = new List<SanPhamFullDTO>();
        //    IDataReader reader = null;
        //    CommonDAL cmDAL = new CommonDAL();
            
        //    try
        //    {
        //        _dbmanager.Open();

        //        _dbmanager.CreateParameters(1);
        //        _dbmanager.AddParameters(0, "@manhomsanpham", manhom);
        //        reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getListSanPhamTheoNhom");

        //        while (reader.Read())
        //        {
        //            lst.Add(createSanPhamFullFromReader(reader));
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //            reader.Close();
        //        _dbmanager.Close();
        //    }
        //    return lst;
        //}


        //// tìm kiếm theo tên sản phẩm
        //public List<SanPhamFullDTO> timKiemSanPhamTheoTen(string tukhoa)
        //{
        //    List<SanPhamFullDTO> lst = new List<SanPhamFullDTO>();
        //    IDataReader reader = null;

        //    try
        //    {
        //        _dbmanager.Open();

        //        _dbmanager.CreateParameters(1);
        //        _dbmanager.AddParameters(0, "@tukhoa", tukhoa);
        //        reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_timKiemSanPham");

        //        while (reader.Read())
        //        {
        //            lst.Add(createSanPhamFullFromReader(reader));
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //            reader.Close();
        //        _dbmanager.Close();
        //    }
        //    return lst;
        //}

        //public List<SanPhamDTO> getListSanPhamToBangGia()
        //{
        //    List<SanPhamDTO> lst = new List<SanPhamDTO>();
        //    IDataReader reader = null;

        //    try
        //    {
        //        _dbmanager.Open();

        //        reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getListSanPhamToBangGia");

        //        while (reader.Read())
        //        {
        //            SanPhamDTO sp = new SanPhamDTO();
        //            sp.MaSanPham = reader["masanpham"].ToString();
        //            sp.TenSanPham = reader["tensanpham"].ToString();
        //            sp.GiaBan = (decimal)reader["giaban"];
        //            //sp.ThongSoKyThuat = reader["thongsokythuat"].ToString();
        //            sp.BaoHanh = reader["baohanh"].ToString();
        //            lst.Add(sp);
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //            reader.Close();
        //        _dbmanager.Close();
        //    }
        //    return lst;
        //}
        #endregion

        #region ===================== Huy ======================

        #region Phương thức getListSanPham_HinhAnh - Đếm số mediaCount
        /// <summary>
        /// Phương thức getListSanPham_HinhAnh - Đếm số mediaCount
        /// </summary>
        /// <returns></returns>
        public List<SanPhamDTO> GetListSanPham_HinhAnh()
        {
            List<SanPhamDTO> _lst = new List<SanPhamDTO>();
            IDataReader _reader = null;
            try
            {
                _dbmanager.Open();

                _reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getListSanPham_HinhAnh");
                while (_reader.Read())
                    _lst.Add(this.CreateSanPham_HinhAnhFromReader(_reader));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (_reader != null)
                    _reader.Close();
            }
            return _lst;
        }
        #endregion

        #region CreateSanPham_HinhAnhFromReader
        /// <summary>
        /// Method CreateSanPham_HinhAnhFromReader
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private SanPhamDTO CreateSanPham_HinhAnhFromReader(IDataReader reader)
        {
            SanPhamDTO sp = new SanPhamDTO();

            sp.MaSanPham = Convert.ToString(reader["MaSanPham"]);
            sp.MaNhomSanPham = Convert.ToString(reader["TenNhomSanPham"]);
            sp.MaNhaSanXuat = Convert.ToString(reader["TenNhaSanXuat"]);
            sp.TenSanPham = Convert.ToString(reader["TenSanPham"]);
            sp.ThongSoKyThuat = Convert.ToString(reader["ThongSoKyThuat"]);
            sp.GiaNhap = Convert.ToDecimal(reader["GiaNhap"]);
            sp.GiaBan = Convert.ToDecimal(reader["GiaBan"]);
            sp.KhuyenMai = (int)reader["KhuyenMai"];
            sp.SoLuongTonKho = (int)reader["SoLuongTonKho"];
            sp.TinhTrang = (int)reader["TinhTrang"];
            sp.SoLuotMua = (int)reader["SoLuotMua"];
            sp.NgayDang = (DateTime)reader["NgayDang"];
            sp.BaoHanh = Convert.ToString(reader["BaoHanh"]);

            return sp;
        }
        #endregion

        #region Phương thức thêm 1 sản phẩm
        /// <summary>
        /// Phương thức thêm 1 sản phẩm
        /// </summary>
        /// <param name="sp">SanPhamDTO</param>
        /// <param name="ha">HinhAnhDTO</param>
        /// <returns>int</returns>
        public int Them(SanPhamDTO sp, HinhAnhDTO ha)
        {
            int _rs = -1;
            try
            {
                _dbmanager.Open();
                _dbmanager.BeginTransaction();
                // 1: Thêm sản phẩm

                _dbmanager.CreateParameters(14);
                _dbmanager.AddParameters(0, "@ma_sp", sp.MaSanPham);
                _dbmanager.AddParameters(1, "@ma_nsp", sp.MaNhomSanPham);
                _dbmanager.AddParameters(2, "@ma_nsx", sp.MaNhaSanXuat);
                _dbmanager.AddParameters(3, "@ten_sp", sp.TenSanPham);
                _dbmanager.AddParameters(4, "@thong_skt", sp.ThongSoKyThuat);
                _dbmanager.AddParameters(5, "@gianhap", sp.GiaNhap);
                _dbmanager.AddParameters(6, "@giaban", sp.GiaBan);
                _dbmanager.AddParameters(7, "@khuyenmai", sp.KhuyenMai);
                _dbmanager.AddParameters(8, "@so_ltk", sp.SoLuongTonKho);
                _dbmanager.AddParameters(9, "@tinhtrang", sp.TinhTrang);
                _dbmanager.AddParameters(10, "@so_lm", sp.SoLuotMua);
                _dbmanager.AddParameters(11, "@so_lx", sp.SoLuotXem);
                _dbmanager.AddParameters(12, "@ngaydang", sp.NgayDang);
                _dbmanager.AddParameters(13, "@baohanh", sp.BaoHanh);

                sp.MaSanPham = Convert.ToString(_dbmanager.ExecuteScalar(CommandType.StoredProcedure, "usp_ThemSanPham"));


                // 2: Thêm image vào table hình ảnh
                _dbmanager.CreateParameters(3);
                _dbmanager.AddParameters(0, "@ma_sp", sp.MaSanPham);
                _dbmanager.AddParameters(1, "@ten_ha", ha.TenHinhAnh);
                _dbmanager.AddParameters(2, "@ha_confirm", ha.HaConfirm);
                //_dbmanager.Parameters[2].Direction = ParameterDirection.Output;
                _rs = _dbmanager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_ThemHinhAnh");
                //rs = (int)_dbmanager.Parameters[2].Value;

                _dbmanager.CommitTransaction();
            }
            catch (Exception ex)
            {
                _dbmanager.RollBackTransaction();
                throw new Exception(ex.Message);
            }
            finally
            {
                if (_dbmanager.Connection.State == ConnectionState.Open)
                    _dbmanager.Close();
            }
            return _rs;
        }
        #endregion

        #region Phương thức xóa 1 sản phẩm
        /// <summary>
        /// Phương thức xóa 1 sản phẩm
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        public int Xoa(SanPhamDTO sp)
        {
            int _index = -1;
            try
            {
                // Mở kết nối
                _dbmanager.Open();

                // Thực thi câu truy vấn
                _dbmanager.CreateParameters(1);
                _dbmanager.AddParameters(0, "@ma_sp", sp.MaSanPham);
                _index = _dbmanager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_XoaSanPham");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (_dbmanager.Connection.State == ConnectionState.Open)
                    _dbmanager.Close();
            }

            return _index;
        }
        #endregion

        #region Phương thức sửa 1 sản phẩm
        /// <summary>
        /// Phương thức sửa 1 sản phẩm
        /// </summary>
        /// <param name="sp"></param>
        /// <param name="ha"></param>
        /// <returns></returns>
        public int Sua(SanPhamDTO sp, HinhAnhDTO ha)
        {
            int _rs = -1;
            try
            {
                _dbmanager.Open();
                _dbmanager.BeginTransaction();
                // 1: Sửa sản phẩm

                _dbmanager.CreateParameters(14);
                _dbmanager.AddParameters(0, "@ma_sp", sp.MaSanPham);
                _dbmanager.AddParameters(1, "@ma_nsp", sp.MaNhomSanPham);
                _dbmanager.AddParameters(2, "@ma_nsx", sp.MaNhaSanXuat);
                _dbmanager.AddParameters(3, "@ten_sp", sp.TenSanPham);
                _dbmanager.AddParameters(4, "@thong_skt", sp.ThongSoKyThuat);
                _dbmanager.AddParameters(5, "@gianhap", sp.GiaNhap);
                _dbmanager.AddParameters(6, "@giaban", sp.GiaBan);
                _dbmanager.AddParameters(7, "@khuyenmai", sp.KhuyenMai);
                _dbmanager.AddParameters(8, "@so_ltk", sp.SoLuongTonKho);
                _dbmanager.AddParameters(9, "@tinhtrang", sp.TinhTrang);
                _dbmanager.AddParameters(10, "@so_lm", sp.SoLuotMua);
                _dbmanager.AddParameters(11, "@so_lx", sp.SoLuotXem);
                _dbmanager.AddParameters(12, "@ngaydang", sp.NgayDang);
                _dbmanager.AddParameters(13, "@baohanh", sp.BaoHanh);

                sp.MaSanPham = Convert.ToString(_dbmanager.ExecuteScalar(CommandType.StoredProcedure, "usp_CapNhatSanPham"));


                // 2: Sửa image ở table hình ảnh
                _dbmanager.CreateParameters(4);
                _dbmanager.AddParameters(0, "@ma_ha", ha.MaHinhAnh);
                _dbmanager.AddParameters(1, "@ma_sp", sp.MaSanPham);
                _dbmanager.AddParameters(2, "@ten_ha", ha.TenHinhAnh);
                _dbmanager.AddParameters(3, "@ha_cf", ha.HaConfirm);
                //_dbmanager.Parameters[2].Direction = ParameterDirection.Output;
                _rs = _dbmanager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_CapNhatHinhAnh");
                //rs = (int)_dbmanager.Parameters[2].Value;

                _dbmanager.CommitTransaction();
            }
            catch (Exception ex)
            {
                _dbmanager.RollBackTransaction();
                throw new Exception(ex.Message);
            }
            finally
            {
                if (_dbmanager.Connection.State == ConnectionState.Open)
                    _dbmanager.Close();
            }
            return _rs;
        }
        #endregion

        #region Phương thức sửa 1 sản phẩm
        /// <summary>
        /// Phương thức sửa 1 sản phẩm
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        public void Sua(SanPhamDTO sp)
        {
            try
            {
                _dbmanager.Open();
                
                _dbmanager.CreateParameters(14);
                _dbmanager.AddParameters(0, "@ma_sp", sp.MaSanPham);
                _dbmanager.AddParameters(1, "@ma_nsp", sp.MaNhomSanPham);
                _dbmanager.AddParameters(2, "@ma_nsx", sp.MaNhaSanXuat);
                _dbmanager.AddParameters(3, "@ten_sp", sp.TenSanPham);
                _dbmanager.AddParameters(4, "@thong_skt", sp.ThongSoKyThuat);
                _dbmanager.AddParameters(5, "@gianhap", sp.GiaNhap);
                _dbmanager.AddParameters(6, "@giaban", sp.GiaBan);
                _dbmanager.AddParameters(7, "@khuyenmai", sp.KhuyenMai);
                _dbmanager.AddParameters(8, "@so_ltk", sp.SoLuongTonKho);
                _dbmanager.AddParameters(9, "@tinhtrang", sp.TinhTrang);
                _dbmanager.AddParameters(10, "@so_lm", sp.SoLuotMua);
                _dbmanager.AddParameters(11, "@so_lx", sp.SoLuotXem);
                _dbmanager.AddParameters(12, "@ngaydang", sp.NgayDang);
                _dbmanager.AddParameters(13, "@baohanh", sp.BaoHanh);

                _dbmanager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_CapNhatSanPham");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }            
        }
        #endregion

        #endregion

        #region Lấy thông tin sản phẩm cho giỏ hàng
        // lay thong tin san pham cho gio hang
        public GioHangDTO getSanPhamVaoGioHang(string masp)
        {
            GioHangDTO gh = new GioHangDTO();
            IDataReader reader = null;
            try
            {
                _dbmanager.Open();

                _dbmanager.CreateParameters(1);
                _dbmanager.AddParameters(0, "@ma_sp", masp);
                reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getSanPhamVaoGioHang");
                while (reader.Read())
                {
                    gh.MaSanPham = reader["MaSanPham"].ToString();
                    gh.TenSanPham = reader["TenSanPham"].ToString();
                    gh.SoLuong = (int)reader["SoLuongTonKho"];
                    gh.GiaBan = Convert.ToDecimal(reader["GiaBan"].ToString());
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return gh;
        }
        #endregion

        #region Lấy toàn bộ thông tin sản phẩm theo mả nhóm sản phẩm
        public List<SanPhamDTO> GetListSanPhamTheoDanhMuc(string madm)
        {
            List<SanPhamDTO> _lst = new List<SanPhamDTO>();
            IDataReader _reader = null;
            try
            {
                _dbmanager.Open();

                _dbmanager.CreateParameters(1);
                _dbmanager.AddParameters(0, "@madm", madm);
                _reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getListSanPhamTheoDanhMuc");
                while (_reader.Read())
                    _lst.Add(this.CreateSanPhamFromReader(_reader));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (_reader != null)
                    _reader.Close();
                _dbmanager.Close();
            }
            return _lst;
        }
        #endregion

        #region Tìm kiếm sản phẩm theo tên
        public List<SanPhamDTO> timKiemSanPhamTheoTen(string tukhoa)
        {
            List<SanPhamDTO> lst = new List<SanPhamDTO>();
            IDataReader reader = null;

            try
            {
                _dbmanager.Open();
                
                _dbmanager.CreateParameters(1);
                _dbmanager.AddParameters(0, "@tukhoa", tukhoa);
                reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_timKiemSanPham");

                while (reader.Read())
                {
                    lst.Add(CreateSanPhamFromReader(reader));
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                _dbmanager.Close();
            }
            return lst;
        }
        #endregion

        #region getListSanPhamToBangGia
        public List<SanPhamDTO> GetListSanPhamToBangGia()
        {
            List<SanPhamDTO> lst = new List<SanPhamDTO>();
            IDataReader reader = null;

            try
            {
                _dbmanager.Open();

                reader = _dbmanager.ExecuteReader(CommandType.StoredProcedure, "usp_getListSanPhamToBangGia");

                while (reader.Read())
                {
                    SanPhamDTO sp = new SanPhamDTO();
                    sp.MaSanPham = reader["masanpham"].ToString();
                    sp.TenSanPham = reader["tensanpham"].ToString();
                    sp.GiaBan = (decimal)reader["giaban"];
                    //sp.ThongSoKyThuat = reader["thongsokythuat"].ToString();
                    sp.BaoHanh = reader["baohanh"].ToString();
                    lst.Add(sp);
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                _dbmanager.Close();
            }
            return lst;
        }
        #endregion
    }
}